package com.byx.chatserver.mapper;

import com.byx.chatserver.dto.*;
import com.byx.chatserver.entity.Friend;
import com.byx.chatserver.entity.Group;
import com.byx.chatserver.entity.User;
import org.apache.ibatis.annotations.*;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

/**
 * UserActionMapper
 *
 * @author Superb
 * @date 2021/4/20 10:01
 */
@Mapper
public interface IUserActionMapper {
    int LIMIT_VALUE = 3;
    /**
     * 新建群聊信息
     * @param group
     */
    @Insert("insert into" +
            " `group` (g_id,g_name,g_owner_id,g_create_time)" +
            " values" +
            " (#{id},#{name},#{ownerId},now())"
            )
    @Options(useGeneratedKeys=true, keyProperty="id", keyColumn="g_id")
    void insertGroup(Group group);

    /**
     * 向群聊用户关联表中插入多条用户信息
     * @param group
     */
    @Insert({
            "<script>",
                    "insert into user_groups (ug_group_id,ug_user_id,ug_join_group_time,ug_user_nick_name)",
                    "values ",
                    "<foreach collection = 'members' item = 'item' separator = ','>",
                    "(#{id},#{item.id},now(),#{item.nickName})",
                    "</foreach>",
            "</script>"
    })
    void insertGroupUsers(Group group);

    /**
     * 向群聊添加用户
     * @param user
     */
    @Insert("insert into user_groups" +
            " (ug_group_id,ug_user_id,ug_join_group_time,ug_user_nick_name,ug_user_role)" +
            " values (#{groupId},#{user.id},now(),#{user.nickName},#{role})")
    void insertGroupUser(@Param("groupId") Long groupId,@Param("user") User user,@Param("role") Integer role);


    @Select("select u_login_id" +
            " from user join `group`" +
            " on" +
            " g_owner_id = u_id" +
            " where g_id = #{groupId};")
    String queryOwnerIdByGroupId(Integer groupId);

    /**
     *
     */
    @Insert("insert into" +
            " friends (f_user_id,f_friend_id,f_remark_name,f_friend_groups_id)" +
            " values (#{id},)")
    void insertFriend(User user,Friend friend);

    /**
     * 通过关键字模糊查询用户账号或昵称含有此关键字的用户，取前3个
     * @param searchKey
     * @return
     */
    @Select("select u_id as id,u_login_id as loginId,u_avatar as avatar,u_description as description," +
            "u_nick_name as nickName,u_age as age,u_sex as sex " +
            "from `user` " +
            "where u_nick_name like '%${searchKey}%' or u_login_id like '%${searchKey}%' limit " + LIMIT_VALUE)
    List<SimpleUserDto> fuzzySearchUserLimited(String searchKey);

    /**
     * 通过关键字模糊查询用户账号或昵称含有此关键字的所有用户
     * @param searchKey
     * @return
     */
    @Select("select u_id as id,u_login_id as loginId,u_avatar as avatar,u_description as description," +
            "u_nick_name as nickName,u_age as age,u_sex as sex " +
            "from `user` " +
            "where u_nick_name like '%${searchKey}%' or u_login_id like '%${searchKey}%'")
    List<SimpleUserDto> fuzzySearchUser(String searchKey);

    /**
     * 通过关键字模糊查询群聊号或名称含有此关键字的所有群聊,取前3个
     * @param searchKey
     * @return
     */
    @Select("select `group`.g_id as id,g_owner_id as ownerId,g_avatar as avatar,g_description as description," +
            "g_name as name,count(ug_group_id) as userCount " +
            "from `group` join user_groups on `group`.g_id = user_groups.ug_group_id " +
            "where `group`.g_name like '%${searchKey}%' or `group`.g_id like '%${searchKey}%' group by ug_group_id limit 3")
    List<SimpleGroupDto> fuzzySearchGroupLimited(String searchKey);
    /**
     * 通过关键字模糊查询群聊号或名称含有此关键字的所有群聊
     * @param searchKey
     * @return
     */
    @Select("select `group`.g_id as id,g_owner_id as ownerId,g_avatar as avatar,g_description as description," +
            "g_name as name,count(ug_group_id) as userCount " +
            "from `group` join user_groups on `group`.g_id = user_groups.ug_group_id " +
            "where `group`.g_name like '%${searchKey}%' or `group`.g_id like '%${searchKey}%' group by ug_group_id")
    List<SimpleGroupDto> fuzzySearchGroup(String searchKey);

    /**
     * 通过用户id获取用户详细信息
     * @param id id
     * @return 用户实体
     */
    @Select("SELECT\n" +
            "\tu_id as id,\n" +
            "\tu_login_id as loginId,\n" +
            "\tu_nick_name as nickName,\n" +
            "\tu_sex as sex,\n" +
            "\tu_age as age,\n" +
            "\tu_description as description,\n" +
            "\tu_birthday as birthday,\n" +
            "\tu_telephone as telephone,\n" +
            "\tu_real_name as realName,\n" +
            "\tu_email as email,\n" +
            "\tu_avatar as avatar,\n" +
            "\tu_vocation as vocation \n" +
            "FROM\n" +
            "`user` \n" +
            "WHERE\n" +
            "\tu_id = #{id}")
    User getUserInfoById(Long id);

    /**
     * 通过账号获取用户详细信息
     * @param loginId 账号
     * @return 用户实体类
     */
    @Select("SELECT\n" +
            "\tu_id as id,\n" +
            "\tu_login_id as loginId,\n" +
            "\tu_nick_name as nickName,\n" +
            "\tu_sex as sex,\n" +
            "\tu_age as age,\n" +
            "\tu_description as description,\n" +
            "\tu_birthday as birthday,\n" +
            "\tu_telephone as telephone,\n" +
            "\tu_real_name as realName,\n" +
            "\tu_email as email,\n" +
            "\tu_avatar as avatar,\n" +
            "\tu_vocation as vocation \n" +
            "FROM\n" +
            "`user` \n" +
            "WHERE\n" +
            "\tu_login_id = #{loginId}")
    User getUserInfoByLoginId(String loginId);
    /**
     * @description 获取用户所有好友分组信息
     *
     * @author superb
     * @date 2021/5/4 14:30
     * @param id
     * @return java.util.List<com.byx.chatserver.dto.FriendGroupDto>
     */
    @Select("select fg_id as groupId,fg_name as groupName from friend_groups where fg_user_id=#{id}")
    List<FriendGroupDto> getUserFriendGroup(Long id);
    /**
     * @description 新增好友分组
     *
     * @author superb
     * @date 2021/5/4 14:29
     * @param groupDto
     * @return void
     */
    @Insert("insert into friend_groups(fg_name,fg_user_id) values (#{groupName},#{userId})")
    @Options(useGeneratedKeys = true,keyColumn = "fg_id",keyProperty = "groupId")
    void insertFriendGroup(FriendGroupDto groupDto);
    /**
     * @description 根据ID查询用户简要信息
     *
     * @author superb
     * @date 2021/5/4 14:25
     * @param id 用户id
     * @return com.byx.chatserver.entity.User
     */
    @Select("select " +
            "\tu_id as id,\n" +
            "\tu_login_id as loginId,\n" +
            "\tu_nick_name as nickName,\n" +
            "\tu_avatar as avatar \n" +
            "FROM\n" +
            "`user` \n" +
            "WHERE\n" +
            "\tu_id = #{id}")
    User queryUserSimpleInfoById(Long id);

    @Update("update " +
            "user set " +
            "u_nick_name='#{nickName}'," +
            "u_sex='#{sex}'," +
            "u_age=#{age} " +
            "u_description='#{description}'," +
            "u_birthday='#{birthday}'," +
            "u_telephone='#{telephone}'," +
            "u_real_name='#{realName}'," +
            "u_email='#{email}'," +
            "u_avatar='#{avatar}'," +
            "u_vocation='#{vocation}'," +
            "where u_id=#{id}")
    void updateUserInfo(User user);

    /**
     * @description 查询用户所在的所有群聊信息
     *
     * @author Superb
     * @date 2021/5/5 14:28
     * @param id 用户id
     * @return java.util.List<com.byx.chatserver.entity.Group>
     */
    @Select("select g_id,g_name,g_avatar " +
            "from `group` \n" +
            "join user_groups ug \n" +
            "on `group`.g_id = ug.ug_group_id where ug_user_id=#{id}")
    List<Group> queryUserGroupById(Long id);

    @Select("select g_id as id, g_name as name, g_avatar as avatar, " +
            "ug_user_role as role\n" +
            "from `group`\n" +
            "    join (select ug_group_id,ug_user_role from user_groups\n" +
            "    where ug_user_id = #{id}) as `ug*` on g_id = `ug*`.ug_group_id")
    List<GroupRole> queryUserGroup(Long id);

//    @Select("select " +
//            "g_id as id, " +
//            "g_name as name, " +
//            "g_description as description, " +
//            "g_owner_id as ownerId, " +
//            "g_avatar as avatar, " +
//            "count(ug_id) as userCount, " +
//            "g_create_time as createTime, " +
//            "ug_user_nick_name as userGroupNickName " +
//            "from `group` " +
//            "join user_groups ug " +
//            "on `group`.g_id = ug.ug_group_id where `group`.g_id = #{id}")

    @Select("select g_id as id, g_name as name, g_description as description, g_owner_id as ownerId, " +
            "g_avatar as avatar, g_create_time as createTime, ug_user_nick_name as userGroupNickName, " +
            "ug_user_role as role, userCount\n" +
            "from `group` join (select ug_group_id,count(ug_id) as userCount from user_groups where ug_group_id = #{groupId}) as ugc\n" +
            "on `group`.g_id = ugc.ug_group_id\n" +
            "left join (select ug_group_id,ug_user_nick_name,ug_user_role from user_groups where ug_user_id = #{userId} and ug_group_id=#{groupId}) as `ug*`\n" +
            "on `group`.g_id = `ug*`.ug_group_id\n" +
            "where g_id = #{groupId};")
    GroupDto queryUserGroupDetailById(@Param("userId") Long userId, @Param("groupId") Long groupId);


    @Select("select u_login_id\n" +
            "from user inner join user_groups\n" +
            "on ug_user_id = u_id\n" +
            "where ug_group_id = #{groupId}")
    List<String> queryGroupMembersLoginId(Integer groupId);

    @Update("update user " +
            "set u_nick_name=#{nickName}," +
            "u_sex=#{sex}," +
            "u_age=#{age}," +
            "u_description=#{description}," +
            "u_birthday=#{birthday}," +
            "u_telephone=#{telephone}," +
            "u_real_name=#{realName}," +
            "u_email=#{email}," +
            "u_avatar=#{avatar}," +
            "u_last_modify_time=#{lastModifyTime} " +
            "where u_id = #{id}")
    void changeUserInfo(User user);

    @Update("update user " +
            "set u_avatar=#{avatar}," +
            "u_last_modify_time=#{timestamp} " +
            "where u_login_id = #{loginId}")
    void changeUserAvatar(String loginId,String avatar,Long timestamp);
}
